﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.SqlClient;
using System.Data.Linq.Mapping;
using IronOne.SalesOne.BusinessObjects;
using System.Configuration;

namespace IronOne.SalesOne.DataObjects
{
    public class LinqGroup
    {
        public int pageSize = Convert.ToInt32(ConfigurationManager.AppSettings["GridCount"]);
        public List<SalesOneGroup> GetGroups(string sortExpression)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<GroupEntity> query = db.GroupEntities;

                if (sortExpression.Length > 0)
                {
                    string[] sort = sortExpression.Split(' ');
                    string sortColumn = sort[0];
                    string sortOrder = sort[1];

                    switch (sortColumn)
                    {
                        case "GroupId":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c =>c.GroupId);
                            else
                                query = query.OrderByDescending(c => c.GroupId);
                            break;
                        case "GroupCode":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.GroupCode);
                            else
                                query = query.OrderByDescending(c => c.GroupCode);
                            break;
                        case "GroupName":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.GroupName);
                            else
                                query = query.OrderByDescending(c => c.GroupName);
                            break;
                    }
                }
                return query.Select(c => Mapper.ToGroupObject(c)).ToList();
            }
        }


        public SalesOneGroup GetGroup(int groupId)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                return Mapper.ToGroupObject(db.GroupEntities
                            .SingleOrDefault(p => p.GroupId == groupId));
            }
        }


        public void InsertGroup(SalesOneGroup group)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    GroupEntity  entity = Mapper.ToGroupEntity(group);
                    db.GroupEntities.InsertOnSubmit(entity);
                    db.SubmitChanges();

                    group.GroupId = entity.GroupId;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to contact record was made before your changes.");
                }
                catch (Exception)
                {
                    throw new Exception("Unable to Insert contact " + group.Name);
                }
            }
        }


        public int UpdateGroup(SalesOneGroup group)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    GroupEntity entity = Mapper.ToGroupEntity(group);
                    db.GroupEntities.Attach(entity, true);
                    db.SubmitChanges();
                    return 1;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to contact record was made before your changes.");
                }
                catch (Exception)
                {
                    return 0;
                }
            }
        }


        public int DeleteGroup(SalesOneGroup group)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    GroupEntity entity = Mapper.ToGroupEntity(group);
                    db.GroupEntities.Attach(entity, false);
                    db.GroupEntities.DeleteOnSubmit(entity);
                    db.SubmitChanges();

                    return 1;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to contact record was made before your changes.");
                }
                catch (Exception)
                {
                    return 0;
                }
            }
        }


        public List<SalesOneGroup> GetSearchGroups(string name,int pageNo)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                //IQueryable<GroupEntity> query = db.GroupEntities;

                var query = from c in db.GroupEntities
                            where SqlMethods.Like(c.GroupName, name)
                            select c;

                var pageData = query.Skip((pageNo-1)*pageSize).Take(pageSize);

                return pageData.Select(c => Mapper.ToGroupObject(c)).ToList();
            }
        }

        public SalesOneGroup GetGroup(string userName)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {

                var query = from groups in db.GroupEntities
                            join usersInStructure in db.UsersInStructureEntities on  groups.GroupId equals usersInStructure.NodeId
                            where usersInStructure.UserTypeId==2
                            join user in db.UserEntities on usersInStructure.UserId equals user.UserId
                            where user.Name==userName
                            select groups;

                return Mapper.ToGroupObject(query.SingleOrDefault());
            }
        }

    }
}
